SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--exec p_msempstartacc 2012,04,-99

alter      procedure p_msempstartacc (
  @nyear int,
  @nmonth int,
  @filid int
)  as  

  declare @empid int,@nfilid int,@subjectid int,@moneyid int
  declare @bmbala  numeric(18,2)
  declare @fbmbala numeric(18,2)
  declare @tmsum   numeric(18,2)
  declare @ftmsum  numeric(18,2)
  declare @local_tab table(empid integer,
                           subjectid integer,
                           moneyid integer,
                           bmbala numeric(18,2),
                           fbmbala numeric(18,2))

begin
  set nocount on
  delete tft_msemployee where nyear=@nyear and nmonth=@nmonth
  declare cur_emp cursor for
  select * from @local_tab
  declare cur_fil cursor for select fi_filid from td_filiale where fi_filid=@filid or @filid =-99
  
  open cur_fil 
  fetch next from  cur_fil
  into  @nfilid;
  while @@fetch_status = 0 
  begin   
    insert into tft_msemployee (empid,subjectid,moneyid,nyear,nmonth,filid,tmsumdebit,tmsumcredit,tmsum,
                          ftmsumdebit,ftmsumcredit,ftmsum)
    select d.empid,d.subjectid,d.moneyid,@nyear,@nmonth,@nfilid,
      sum(dcdebitamt) tmsumdebit,sum(dccreditamt) tmsumcredit, sum((dcdebitamt-dccreditamt)*sign(abs(m.formertype-2002))) tmsum,
      sum(debitamt) ftmsumdebit,sum(creditamt) ftmsumcredit, sum((debitamt-creditamt)*sign(abs(m.formertype-2002))) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doemp=1 
      and d.empid is not null
      and (d.voucherid<0 and (d.borderno=2 or d.borderno=3))
      group by  d.subjectid,d.empid,d.moneyid

     delete from @local_tab
     insert into @local_tab
     select d.empid,d.subjectid,d.moneyid, 
       sum((s.isdebit*2-1)*(dcdebitamt+dccreditamt)) bmbala,
       sum((s.isdebit*2-1)*(debitamt+creditamt)) fbmbala
     from tfb_voucherdtl d
     inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
     inner join tfd_subject s on d.subjectid=s.subjectid
     where s.doemp=1 and d.empid is not null
           and (d.voucherid<0 and d.borderno=1)
     group by  d.subjectid,d.empid,d.moneyid


     open  cur_emp
     fetch next from  cur_emp
     into @empid,@subjectid,@moneyid,@bmbala,@fbmbala
     while @@fetch_status = 0 
       begin
         update tft_msemployee set bmbala =@bmbala, fbmbala=@fbmbala
         where empid = @empid and subjectid = @subjectid and moneyid=@moneyid and filid=@nfilid
               and nyear=@nyear and nmonth=@nmonth
         fetch next from cur_emp
         into @empid,@subjectid,@moneyid,@bmbala,@fbmbala
       end
     close  cur_emp
     delete from @local_tab

    insert into @local_tab
    select d.empid,d.subjectid,d.moneyid,sum(dcdebitamt) tmsum,
           sum(debitamt) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid=@nfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doemp=1 and d.empid is not null
            and (d.voucherid<0 and d.borderno=4)
    group by  d.subjectid,d.empid,d.moneyid
    open  cur_emp 
    fetch next from  cur_emp
    into  @empid,@subjectid,@moneyid,@tmsum,@ftmsum
    while @@fetch_status = 0
      begin
        update tft_msemployee set tmsum = @tmsum,ftmsum = @ftmsum
        where empid = @empid and subjectid = @subjectid and moneyid=@moneyid and filid=@nfilid
              and nyear=@nyear and nmonth=@nmonth
        fetch next from  cur_emp
        into @empid,@subjectid,@moneyid,@bmbala,@fbmbala
      end
    close  cur_emp
    fetch next from  cur_fil
    into  @nfilid;
  end--end filid
  deallocate  cur_emp
  close cur_fil;
  deallocate  cur_fil;
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

